


	DECLARE @ReportType	smallint, @AppType	smallint, @ReportDate	varchar(50), @AgentId	bigint, @UserTypeId int;
	SET @ReportType = 0
	SET @AppType = 1
	SET @ReportDate = '20130701'
	SET @AgentId = 0
	SET @UserTypeId = 400
	DECLARE @INSERT VARCHAR(3000),@SELECT VARCHAR(3500),@SelectedDate VARCHAR(500),@SelectedAgentId VARCHAR(100), @WHARE VARCHAR(MAX), @UserType BIGINT;
	DECLARE @I INT,@RC INT, @CollectAmount FLOAT, @CommPer FLOAT, @CommAmount FLOAT, @PremiumLevel NVARCHAR(50),@Incentive FLOAT, @IncentivePer BIGINT;
	DECLARE @OLCommission INT, @App VARCHAR(10), @Agent BIGINT, @EffDate DATETIME, @RptDate DATETIME, @Day INT, @AC INT, @A INT, @RefId BIGINT;

	SET @SELECT = ''
	SET @WHARE = ''
	SET @SelectedDate = ''
	SET @SelectedAgentId = ''	
	
	BEGIN --A1
		DECLARE @@IndAgentCollection TABLE
		(
			RefId			BIGINT IDENTITY(1,1),
			AgentId			BIGINT,			
			AgentName		NVARCHAR(250),
			OrderDate		DATETIME,
			TypeOfLeads		VARCHAR(10),
			ApplicationName	NVARCHAR(100),
			Insurer			NVARCHAR(50),
			InsuranceType	VARCHAR(15),
			CarPlate		NVARCHAR(50),
			QuoteRefNo		VARCHAR(50),			
			VMI				FLOAT,
			CMI				FLOAT,
			CollectedAmount	FLOAT,
			TotalAmount		FLOAT,
			CommissionPer	FLOAT,
			Commission		FLOAT,
			Incentive		FLOAT,
			Total			FLOAT,
			CollectedType	SMALLINT,
			OLCommission	INT
		)
		DECLARE @@AllIndAgentCollection TABLE
		(
			RefId			BIGINT IDENTITY(1,1),
			AgentId			BIGINT,			
			AgentName		NVARCHAR(250),
			OrderDate		DATETIME,
			TypeOfLeads		VARCHAR(10),
			ApplicationName	NVARCHAR(100),
			Insurer			NVARCHAR(50),
			InsuranceType	VARCHAR(15),
			CarPlate		NVARCHAR(50),
			QuoteRefNo		VARCHAR(50),			
			VMI				FLOAT,
			CMI				FLOAT,
			CollectedAmount	FLOAT,
			TotalAmount		FLOAT,
			CommissionPer	FLOAT,
			Commission		FLOAT,
			Incentive		FLOAT,
			Total			FLOAT,
			CollectedType	SMALLINT,
			OLCommission	INT
		)
		
		DECLARE @@AgentList TABLE
		(
			RefId			BIGINT IDENTITY(1,1),
			AgentId			BIGINT
		)
		
		DECLARE @@AgentCommission TABLE
		(
			RefId			BIGINT IDENTITY(1,1),
			AgentId			BIGINT,
			BrokerId		INT,
			BrokerName		NVARCHAR(50),
			AgentNameTH		NVARCHAR(250),			
			AgentNameEN		NVARCHAR(250),			
			VMI				FLOAT,
			CMI				FLOAT,
			CollectedAmount	FLOAT,
			CommissionPer	FLOAT,
			Commission		FLOAT,
			Incentive		FLOAT,
			Total			FLOAT,
			ApplicationType	VARCHAR(10),
			OLCommission	INT
		)
		IF(@AgentId > 0)
		BEGIN
			SET @SelectedAgentId =  ' AND BR.AgentId IS NOT NULL AND (BR.AgentId  = '+ CAST(@AgentId AS VARCHAR)+ ')'   
		END
		
		IF(@ReportType = 0)
		BEGIN
			SET @SelectedDate =  ' AND CONVERT(VARCHAR(6), TR.ModifiedDate, 112) = ' + LEFT(@ReportDate,6) 
		END	
		ELSE IF(@ReportType = 1)
		BEGIN
			SET @SelectedDate = ' AND CONVERT(VARCHAR(8), TR.ModifiedDate, 112) = ' + @ReportDate 
		END
		
		IF(@AppType = 1)
		BEGIN
			SET @WHARE =  ' AND (CM.Info1 = ''NEW'' OR CM.Info1 = ''CHB'')' 
		END	
		ELSE IF(@AppType = 2)
		BEGIN
			SET @WHARE =  ' AND (CM.Info1 = ''REN'' OR CM.Info1 = ''RNO'')' 
		END
		
		IF (@UserTypeId IS NULL OR @UserTypeId = 400)
		BEGIN
			SET @UserType = 400
		END
		ELSE IF (@UserTypeId IS NOT NULL AND @UserTypeId = 1001)
		BEGIN
			SET @UserType = 1001
		END
		
		SELECT @SELECT = 'SELECT BR.AgentId,(CONVERT(VARCHAR(100),DECRYPTBYKEY(UP.EInfo1)) + '' '' + CONVERT(VARCHAR(100),DECRYPTBYKEY(UP.EInfo2))),
		CONVERT(VARCHAR,TR.ModifiedDate,106),CASE WHEN CM.Info1 = ''REN'' THEN ''Renew'' WHEN CM.Info1 = ''RNO'' THEN ''Renew'' ELSE ''Online'' END,
		CM.Name ApplicationName,CASE WHEN CM.ProviderCompanyId = 11 THEN ''AZCP'' WHEN CM.ProviderCompanyId = 12 THEN ''MSIG''
		WHEN CM.ProviderCompanyId = 16 THEN ''AXA'' WHEN CM.ProviderCompanyId = 29 THEN ''LMG'' WHEN CM.ProviderCompanyId = 31 THEN ''CTS''
		WHEN CM.ProviderCompanyId = 32 THEN ''BKI'' WHEN CM.ProviderCompanyId = 33 THEN ''MTI'' WHEN CM.ProviderCompanyId = 35 THEN ''KPI''
		WHEN CM.ProviderCompanyId = 39 THEN ''MIR'' WHEN CM.ProviderCompanyId = 41 THEN ''KSK'' WHEN CM.ProviderCompanyId = 43 THEN ''SMK'' 
		WHEN CM.ProviderCompanyId = 45 THEN ''VRY'' WHEN CM.ProviderCompanyId = 46 THEN ''TMS'' WHEN CM.ProviderCompanyId = 50 THEN ''SCS'' 
		WHEN CM.ProviderCompanyId = 58 THEN ''TVV'' WHEN CM.ProviderCompanyId = 65 THEN ''DPY'' END, 
		CASE WHEN CM.InsuranceType = 10 THEN ''Voluntary 1'' WHEN CM.InsuranceType = 11 THEN ''Voluntary 1+'' 
		WHEN CM.InsuranceType = 21 THEN ''Voluntary 2+'' WHEN CM.InsuranceType = 30 THEN ''Voluntary 3''
		WHEN CM.InsuranceType = 31 THEN ''Voluntary 3+'' END,CM.CarPlate,CM.QuoteRefNo,
		CASE WHEN BR.ApprovedPremium IS NOT NULL AND BR.ApprovedPremium >= 35000 AND BR.IsIncludeCMI = 1 THEN CAST(BR.ApprovedPremium AS NUMERIC(10,2))
		WHEN BR.ApprovedPremium IS NULL AND BR.FinalAnnualPremium >= 35000 AND BR.IsIncludeCMI = 1 THEN CAST(BR.FinalAnnualPremium AS NUMERIC(10,2))
		WHEN BR.ApprovedPremium IS NOT NULL THEN CAST((BR.ApprovedPremium - BR.TotalCMI) AS NUMERIC(10,2)) 
		ELSE CAST((BR.FinalAnnualPremium - BR.TotalCMI) AS NUMERIC(10,2)) END,CAST(BR.TotalCMI AS NUMERIC(10,2)),
		CASE WHEN BR.ApprovedPremium IS NOT NULL THEN CAST(BR.ApprovedPremium AS NUMERIC(10,2)) ELSE CAST(BR.FinalAnnualPremium AS NUMERIC(10,2)) END,
		0,0,0,0,0,1,0
		FROM tblNonGGCarMoreDetails CM 
		INNER JOIN tblCXBrokerProvider BP ON BP.CXBrokerProviderId = CM.ProviderCompanyId
		INNER JOIN tblCXBrokerPremium BR ON BR.CECarId = CM.CECarId AND BR.CXBrokerProviderId = CM.ProviderCompanyId AND BR.SelectedStatus = 1
		INNER JOIN tblCEUser CU ON CU.CEID = CM.RefCEUserId  
		INNER JOIN tblCEUsrProfile UP ON UP.RefCEUserId = BR.AgentId 
		INNER JOIN tblCEContact CC ON CC.RefCEUserId = BR.AgentId
		INNER JOIN tblNonGGTransaction TR ON TR.CECarId = CM.CECarId AND TR.TransactionTypeId = 100
		WHERE	CM.[Status] = 1 AND (CM.CancelStatus IS NULL OR CM.CancelStatus = 0) AND CM.PaymentStatus = 1 AND CU.UsrTypeId = ' +  CAST(@UserType AS VARCHAR)
		
		OPEN SYMMETRIC KEY CXUserSym
		DECRYPTION BY CERTIFICATE CXUserCert	
		IF @AgentId > 0
		BEGIN
			INSERT INTO @@IndAgentCollection
			EXEC (@SELECT + @WHARE + @SelectedDate + @SelectedAgentId)
		END
		ELSE
		BEGIN
			INSERT INTO @@AllIndAgentCollection
			EXEC (@SELECT + @WHARE + @SelectedDate + @SelectedAgentId)		
		END
		CLOSE SYMMETRIC KEY CXUserSym
		
		DECLARE @TotalAmount FLOAT, @Amount FLOAT--, @LIST VARCHAR(MAX);
		--SET @LIST = '0'
		--BEGIN --S1
			IF 0 < (SELECT COUNT(RefId) FROM @@AllIndAgentCollection)
			BEGIN --S2
				INSERT INTO @@AgentList
				SELECT AgentId FROM @@AllIndAgentCollection GROUP BY AgentId
				SET @AC = 0
				SELECT @AC = (SELECT COUNT(RefId) FROM @@AgentList)
				--SELECT @AC
				
				SET @A = 1
				WHILE (@A <= @AC)
				BEGIN --S3
					SET @Agent = 0
					SELECT @Agent = AgentId FROM @@AgentList WHERE RefId = @A
					--SET @LIST = @LIST + ',' + CAST(@Agent AS VARCHAR)
					IF @Agent > 0
					BEGIN --S4					
						DELETE FROM @@IndAgentCollection

						INSERT INTO @@IndAgentCollection
						SELECT AgentId,AgentName,OrderDate,TypeOfLeads,ApplicationName,Insurer,InsuranceType,CarPlate,QuoteRefNo,VMI,CMI,CollectedAmount,
								TotalAmount,CommissionPer,Commission,Incentive,Total,CollectedType,OLCommission 
						FROM	@@AllIndAgentCollection 
						WHERE	AgentId = @Agent
						
						/* Find Fresher Agent */
						SET @OLCommission = 0	
						SELECT @EffDate = CONVERT(DATETIME,Info10,112) FROM tblCEUser WHERE UsrTypeId = 200 AND LEN(Info10) > 0 AND CEID = @Agent
						IF @EffDate IS NOT NULL
						BEGIN --S5
							SET @Day = 0
							SELECT @Day = DAY(@EffDate)
							IF CONVERT(VARCHAR(6),@RptDate,112) = CONVERT(VARCHAR(6),@EffDate,112)
							BEGIN --S6
								UPDATE @@IndAgentCollection SET OLCommission = 1 --WHERE AgentId = @Agent
								SET @OLCommission = 1
							END --E6
							IF CONVERT(VARCHAR(6),@RptDate,112) = CONVERT(VARCHAR(6),DATEADD(mm,1,@EffDate),112)
							BEGIN --S7
								IF @Day > 5
								BEGIN --S8
									UPDATE @@IndAgentCollection SET OLCommission = 1 --WHERE AgentId = @Agent
									SET @OLCommission = 1
								END --E8
							END --E7
						END --E5
						
						SET @TotalAmount = 0
						SELECT @TotalAmount = SUM(CollectedAmount) FROM @@IndAgentCollection WHERE TypeOfLeads = 'Online' --AND AgentId = @Agent
						IF @TotalAmount IS NULL SET @TotalAmount = 0
						UPDATE @@IndAgentCollection SET TotalAmount = @TotalAmount  WHERE TypeOfLeads = 'Online' --AND AgentId = @Agent
						
						SET @CommPer = 0
						SET @CommAmount = 0
						SET @CollectAmount = @TotalAmount
						SET @PremiumLevel = '0'
						IF @CollectAmount >= 1 AND @CollectAmount <= 199999
						BEGIN --S11	
							SET @PremiumLevel = '0'
						END --E11
						ELSE IF @CollectAmount >= 200000 AND @CollectAmount <= 349999
						BEGIN --S12	
							SET @PremiumLevel = '200000'
						END --E12
						ELSE IF @CollectAmount >= 350000 AND @CollectAmount <= 499999
						BEGIN --S13		
							SET @PremiumLevel = '350000'
						END --E13
						ELSE IF @CollectAmount >= 500000 AND @CollectAmount <= 599999
						BEGIN --S14		
							SET @PremiumLevel = '500000'
						END --E14
						ELSE IF @CollectAmount >= 600000 AND @CollectAmount <= 699999
						BEGIN --S15	
							SET @PremiumLevel = '600000'
						END --E15
						ELSE IF @CollectAmount >= 700000 AND @CollectAmount <= 799999
						BEGIN --S16		
							SET @PremiumLevel = '700000'
						END --E16
						ELSE IF @CollectAmount >= 800000 AND @CollectAmount <= 899999
						BEGIN --S17		
							SET @PremiumLevel = '800000'
						END --E17
						ELSE IF @CollectAmount >= 900000 
						BEGIN --S18	
							SET @PremiumLevel = '900000'
						END --E18					
				
						SELECT	@CommPer = Commission
						FROM	tblCXCRMAgentCommission 
						WHERE	PremiumLevel = @PremiumLevel
								AND (CONVERT(VARCHAR(8),EffectiveStartDate,112) <= @ReportDate AND CONVERT(VARCHAR(8),EffectiveEndDate,112) >= @ReportDate) 
								AND [Status] = 1
						IF @CommPer IS NULL SET @CommPer = 0 
						
						SET @RC = 0

						--SELECT COUNT(RefId) FROM @@IndAgentCollection
						--SELECT @RC = (SELECT COUNT(RefId) FROM @@AgentCollectionList)
						SELECT @RC = (SELECT COUNT(RefId) FROM @@IndAgentCollection)
						
						Select 'BEFORE', @CommPer, * from @@IndAgentCollection

						SET @I = 1
						WHILE (@I <= @RC)
						BEGIN --S19
							--SET @RefId = 0
							--SELECT @RefId = ForRefId FROM @@AgentCollectionList WHERE RefId = @I
							--Select @RC RC, '1'
							
							SET @Amount = 0
							SET @CommAmount = 0
							SELECT @Amount = CollectedAmount FROM @@IndAgentCollection WHERE RefId = @I --AND AgentId = @Agent
							
							SELECT @I, '@CommAmount = (@Amount * @CommPer) / 100.0', @CommAmount CommAmount, @Amount [Amount], @CommPer CommPer
							Select * FROM @@IndAgentCollection WHERE RefId = @I

							IF @Amount IS NULL SET @Amount = 0

							

							IF @Amount > 0
							BEGIN --S20
								--Select @RC RC, '2'
								SELECT @CommAmount = (@Amount * @CommPer) / 100.0
								

								SELECT	@Incentive = Incentive
								FROM	tblCXCRMIncentive 
								WHERE	(@Amount BETWEEN [From] AND [To]) AND Employee = 'ONL'
										AND (CONVERT(VARCHAR(8),EffectiveStartDate,112) <= @ReportDate AND CONVERT(VARCHAR(8),EffectiveEndDate,112) >= @ReportDate) 
										AND [Status] = 1
								IF @Incentive IS NULL SET @Incentive = 0
							END --E20
							UPDATE	@@IndAgentCollection 
							SET		CommissionPer = @CommPer, Commission = @CommAmount, Incentive = @Incentive 
							WHERE	RefId = @I --AND TypeOfLeads = 'Online'  --AND AgentId = @Agent
							SET @I = @I + 1
						END --E19

						Select 'AFTER', @CommPer, * from @@IndAgentCollection
						UPDATE @@IndAgentCollection SET Total = (Commission + Incentive)
						
						INSERT INTO @@AgentCommission(AgentId,AgentNameTH,VMI,CMI,CollectedAmount,Commission,Incentive,Total)
						SELECT	AgentId,AgentName,SUM(VMI),SUM(CMI),SUM(CollectedAmount),SUM(Commission),SUM(Incentive),SUM(Total)
						FROM	@@IndAgentCollection 
						WHERE	 AgentId = @Agent 
						GROUP BY AgentId,AgentName
						UPDATE	AC 
						SET		AC.AgentNameEN		= UP.Info1, 
								AC.BrokerId			= CASE WHEN CC.Info1 = 'Cashier' THEN 21 WHEN CC.Info1 LIKE 'Admin%' THEN 22 ELSE SUBSTRING(CC.Info1, 7, 2) END,
								AC.BrokerName		= CC.Info1,
								AC.ApplicationType	= @AppType,
								AC.OLCommission		= 0,
								AC.CommissionPer	= @CommPer
						FROM	@@AgentCommission AC 
								INNER JOIN tblCEUsrProfile UP ON UP.RefCEUserId = AC.AgentId 
								INNER JOIN tblCEContact CC ON CC.RefCEUserId = AC.AgentId
						WHERE	AC.AgentId = @Agent
					END --E4
					SET @A = @A + 1
				END --E3
				/* Fresher Agent - Commission - End */		
				SELECT * FROM @@AgentCommission ORDER BY BrokerId,ApplicationType 
				--SELECT * FROM @@AllIndAgentCollection ORDER BY AgentId
				SELECT * FROM @@AgentList ORDER BY RefId 
			END --E2
		--END --E1
		/* ALL AGENT COMMISSION - END */ 	
	END --A1
		
		 